/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import model.entities.Student;
import model.entities.Student_CourseDetails;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.Student_CourseDetailsDAOService;
import model.entities.Course;

/**
 *
 * @author K u Ku
 */
public class Student_CourseDetailsDAO implements Student_CourseDetailsDAOService {
    private static Student_CourseDetailsDAO studentCourseDAO;
    
    public static Student_CourseDetailsDAO getInstance(){
        if(studentCourseDAO==null){
            studentCourseDAO = new Student_CourseDetailsDAO();
        }
        return studentCourseDAO;
    }
    
    // Function get all Stuent_CourseDetail
    @Override
    public List<Student_CourseDetails> getStudent_CourseDetailsList(){
        ArrayList<Student_CourseDetails> lstDetails = new ArrayList<Student_CourseDetails>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblStudent_CourseDetails";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student_CourseDetails details = new Student_CourseDetails();
                details.setStudent_CourseDetailsID(rs.getInt("Student_CourseDetailsID"));
                details.setRegisterDate(rs.getDate("RegisterDate"));
                details.setStopDate(rs.getDate("StopDate"));
                Student stu = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                details.setStudent(stu);
                model.entities.Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                details.setCourse(c);
                lstDetails.add(details);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstDetails;
    }
    
    //Function get Details by ID
    @Override
    public Student_CourseDetails getStudent_CourseDetailsByID(int detailsID){
        Student_CourseDetails details = new Student_CourseDetails();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblStudent_CourseDetails WHERE Student_CourseDetailID= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, detailsID);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                details.setStudent_CourseDetailsID(rs.getInt("Student_CourseDetailsID"));
                details.setRegisterDate(rs.getDate("RegisterDate"));
                details.setStopDate(rs.getDate("StopDate"));
                Student stu = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                details.setStudent(stu);
                model.entities.Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                details.setCourse(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return details;
    }

    @Override
    public boolean insertStudent_CourseDetails(Student_CourseDetails scd) {
        String query = "INSERT INTO tblStudent_CourseDetails VALUES(?,?,?,?)";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, scd.getStudent().getStudentID());
            pstmt.setInt(2, scd.getCourse().getCourseID());
            pstmt.setDate(3, (Date) scd.getRegisterDate());
            pstmt.setDate(4, (Date) scd.getStopDate());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean updateStudent_CourseDetails(Student_CourseDetails scd) {
        String query = "UPDATE tblStudent_CourseDetails SET StudentID=?, CourseID=?, RegisterDate=?, StopDate=?" +
                        "WHERE Student_CourseDetailID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, scd.getStudent().getStudentID());
            pstmt.setInt(2, scd.getCourse().getCourseID());
            pstmt.setDate(3, (Date) scd.getRegisterDate());
            pstmt.setDate(4, (Date) scd.getStopDate());
            pstmt.setInt(5, scd.getStudent_CourseDetailsID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean deleteStudent_CourseDetails(int id) {
        String query = "DELETE tblStudent_CourseDetails WHERE Student_CourseDetailID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
    }

    @Override
    public List<Student_CourseDetails> findByID(int id) {
        List<Student_CourseDetails> lstDetails = new ArrayList<Student_CourseDetails>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblStudent_CourseDetails WHERE Student_CourseDetailID= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student_CourseDetails scd = new Student_CourseDetails();
                scd.setStudent_CourseDetailsID(rs.getInt("Student_CourseDetailsID"));
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                scd.setCourse(c);
                Student s = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                scd.setStudent(s);
                scd.setRegisterDate((Date) rs.getDate("RegisterDate"));
                scd.setStopDate(rs.getDate("StopDate"));
                lstDetails.add(scd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstDetails;
    }

    @Override
    public List<Student_CourseDetails> findByRegisterDate(java.util.Date date) {
        List<Student_CourseDetails> lstDetails = new ArrayList<Student_CourseDetails>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblStudent_CourseDetails WHERE RegisterDate= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setDate(1,(Date) date);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student_CourseDetails scd = new Student_CourseDetails();
                scd.setStudent_CourseDetailsID(rs.getInt("Student_CourseDetailsID"));
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                scd.setCourse(c);
                Student s = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                scd.setStudent(s);
                scd.setRegisterDate((Date) rs.getDate("RegisterDate"));
                scd.setStopDate(rs.getDate("StopDate"));
                lstDetails.add(scd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstDetails;
    }

    @Override
    public List<Student_CourseDetails> findByStopDate(java.util.Date date) {
        List<Student_CourseDetails> lstDetails = new ArrayList<Student_CourseDetails>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblStudent_CourseDetails WHERE StopDate= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setDate(1,(Date) date);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student_CourseDetails scd = new Student_CourseDetails();
                scd.setStudent_CourseDetailsID(rs.getInt("Student_CourseDetailsID"));
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                scd.setCourse(c);
                Student s = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                scd.setStudent(s);
                scd.setRegisterDate((Date) rs.getDate("RegisterDate"));
                scd.setStopDate(rs.getDate("StopDate"));
                lstDetails.add(scd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Student_CourseDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstDetails;
    }

    @Override
    public List<Course> getCourseByStudentID(int studentID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Student> getStudentByCourseID(int courseID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}
